﻿using System;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Windows.Forms;
using CodeGenerator.interfaces;
using System.Data.SqlClient;
using CodeGenerator;
namespace CodeGenerator.Implementations
{
    public class CodeGenerator : ICodeGenerator
    {
        private IGeneratorModel _model;
        private readonly string _OutputFolder;
        private readonly string _OutputFolderProject;
        private readonly string _OutputFolderSampleProject;
        private TemplateProvider _templateProvider;
        private DatabaseEngines _databaseEngine;
        //Source code container variables
        String _classSourceCode = "";
        String _dataClassSourceCode = "";
        String _dataAccessClassSourceCode = "";

        public CodeGenerator(IGeneratorModel Model, string BaseOutputFolder, DatabaseEngines databaseEngine)
        {
            _databaseEngine = databaseEngine;
            _model = Model;
            _OutputFolder = BaseOutputFolder;

            if (!_OutputFolder.EndsWith(@"\"))
                _OutputFolder += @"\";

            _OutputFolderProject = _OutputFolder + Model.NameSpace + @"\";
            _OutputFolderSampleProject = _OutputFolder + Model.NameSpace + @".SampleProject\";

            _templateProvider = new TemplateProvider();
        }

        public void GenerateClassesSourceCode()
        {
            foreach (IGeneratorClass newClass in _model.Classes)
            {
                GenerateClassSourceCode(newClass);
            }
            GenerateProjectSourceCode();
        }
        
        private string GenerateAssemblyInfo()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(_templateProvider.GetAssemblyInfoSourceCode(_model));
            return sb.ToString();
        }
        
        private string GenerateProjectSourceCode()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(_templateProvider.GetProjectSourceCode(_model));
            return sb.ToString();
        }
        
        private string GenerateProjectSampleSourceCode()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(_templateProvider.GetProjectSampleSourceCode(_model));
            return sb.ToString();
        }

        private string GenerateProjectSample_ProgramCS_SourceCode()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(_templateProvider.GetSampleProject_ProgramCS_SourceCode(_model));
            return sb.ToString();
        }
        private string GetSampleProject_MainFormCS_SourceCode()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(_templateProvider.GetSampleProject_MainFormCS_SourceCode(_model));
            return sb.ToString();
        }  
        private string GetSampleProject_MainFormRESX_SourceCode()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(_templateProvider.GetSampleProject_MainFormRESX_SourceCode(_model));
            return sb.ToString();
        }  
        
        private string GetSampleProject_Solution_SourceCode()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(_templateProvider.GetSampleProject_Solution_SourceCode(_model));
            return sb.ToString();
        }
        

        public string GetPrimaryKeyWhereCondition(IDataBaseTable DatabaseTable)
        {
            string res = "";
            foreach (var databaseColumn in DatabaseTable.PrimaryKey)
            {
                res += "[" + databaseColumn.ColumnName + "] = @" + databaseColumn.ColumnName + " AND";
            }

            if (DatabaseTable.PrimaryKey.Count > 0)
                res = res.Substring(0, res.Length - 4);
            return res;
        }
        
        private void GenerateClassSourceCode(IGeneratorClass NewClass)
        {
            
            StringBuilder sbClassPrivateVariables = new StringBuilder();
            StringBuilder sbClassConstructors = new StringBuilder();
            StringBuilder sbClassPublicProperties = new StringBuilder();
            StringBuilder sbClassForeignKeysPrivateVariables = new StringBuilder();
            StringBuilder sbDataClassPrivateVariables = new StringBuilder();
            StringBuilder sbDataClassPublicProperties = new StringBuilder();
            StringBuilder sbDataClassConstructors = new StringBuilder();
            String sbDataAccessClassPrivateVariables = "";
             
            StringBuilder sbForeignKeysProperties = new StringBuilder();
            StringBuilder sbDataAccessClassDataLoaders = new StringBuilder();
            StringBuilder sbDataAccessClassColumnNames = new StringBuilder();
            StringBuilder sbDataAccessClassParametersNamesUpdateMethod = new StringBuilder();
            StringBuilder sbDataAccessClassCheckIfPrimaryKeyIsSupplied = new StringBuilder();
            StringBuilder sbDataAccessClassAutoGeneratedIdsToDataClassAssignation = new StringBuilder();
            
            StringBuilder sbDataAccessClassParametersNamesInsertMethod = new StringBuilder();
            StringBuilder sbDataAccessClassInsertMethodParametersAssignation = new StringBuilder();
            StringBuilder sbDataAccessClassRemoveMethodsParametersAssignation = new StringBuilder();
            StringBuilder sbDataAccessClassUpdateMethodParametersAssignationSqlIte = new StringBuilder();
            StringBuilder sbDataAccessClassConstructors = new StringBuilder();
            StringBuilder sbDataAccessClassCreateFromDataRowMethodColumns = new StringBuilder();
            StringBuilder sbDataAccessClassUpdateMethodColumns = new StringBuilder();
            String sbDataAccessClassCreateFromDataRow = "";
            String sbDataAccessClassUpdateMethod = "";
            String sbDataAccessClassInsertMethod = "";
            String sbDataAccessClassGetAllMethod = "";
            String sbDataAccessClassRemoveMethods = "";

            string DataBaseSpecificUsings = "";
            //StringBuilder sbDataAccessClassInsertMethodColumns = new StringBuilder();

            if(_databaseEngine == DatabaseEngines.SqlServer)
            {
                DataBaseSpecificUsings = "using System.Data.SqlClient;";
            }
            int propertiesCounter = 0;
            foreach (IProperty prop in NewClass.Properties)
            {
                sbClassPrivateVariables.Append(_templateProvider.GetClassPropertyLocalVariableSourceCode(prop, _model));
                sbDataClassPrivateVariables.Append(_templateProvider.GetDataClassPropertyLocalVariableSourceCode(prop,_model));

                sbDataAccessClassCreateFromDataRowMethodColumns.Append(GetDataAccessClassCreateFromDataRowVariablesInitialization(prop));

                sbDataAccessClassUpdateMethodColumns.Append(GetDataAccessClassUpdateMethodVariablesSetter(prop));
                //sbDataAccessClassInsertMethodColumns.Append(GetDataAccessClassInsertMethodVariablesSetter(prop));
               

                sbDataAccessClassColumnNames.Append("\"" + prop.DatabaseColumn.ColumnName + "\", ");
                if(prop.IsPrimaryKey())
                {
                    sbDataAccessClassParametersNamesUpdateMethod.Append("(CASE WHEN @" + prop.DatabaseColumn.ColumnName + " = -1 THEN (select ifnull(max(t." + prop.DatabaseColumn.ColumnName + ") +1 ,1) from " + prop.ContainerTable.TableName + " t) ELSE @" + prop.DatabaseColumn.ColumnName + " END), ");
                }
                else
                {
                    sbDataAccessClassParametersNamesUpdateMethod.Append("@" + prop.DatabaseColumn.ColumnName + ", ");
                    
                }

                string dataAccessClassInsertUpdateMethodParameterToDbCommand = _templateProvider.GetDataAccessClassGenericMethodParameterToDbCommand(propertiesCounter, "@" + prop.DatabaseColumn.ColumnName, prop.DatabaseColumn.DataType.Name, "OwnData." + prop.PropertyName, "            ");
                sbDataAccessClassInsertMethodParametersAssignation.Append(dataAccessClassInsertUpdateMethodParameterToDbCommand);
                sbDataAccessClassUpdateMethodParametersAssignationSqlIte.Append(dataAccessClassInsertUpdateMethodParameterToDbCommand);
                
                //Set
                prop.SetSourceCode(OutputType.Class, _templateProvider.GetClassPropertySourceCode(prop));
                prop.SetSourceCode(OutputType.DataClass, _templateProvider.GetDataClassPropertySourceCode(prop));
                //Get
                sbClassPublicProperties.Append(prop.GetSourceCode(OutputType.Class));
                sbDataClassPublicProperties.Append(prop.GetSourceCode(OutputType.DataClass));
                propertiesCounter++;
            }
            sbDataAccessClassParametersNamesInsertMethod.Append(sbDataAccessClassParametersNamesUpdateMethod.ToString());
            int primaryKeyColumnsCounter = 0;
            foreach (var databaseColumn in NewClass.DatabaseTable.PrimaryKey)
            {   

                sbDataAccessClassParametersNamesUpdateMethod.Append("@" + databaseColumn.ColumnName + ", ");
                var prop = (from Property pi in NewClass.Properties where pi.DatabaseColumn.ColumnName == databaseColumn.ColumnName select pi).Single();

                sbDataAccessClassAutoGeneratedIdsToDataClassAssignation.Append(GetPropertyNewGeneratedAutoincrementalValue(prop));
                sbDataAccessClassCheckIfPrimaryKeyIsSupplied.Append(GetPropertyDefaultValueChecker(prop));
                string sqLiteDatePatch = "";
                if (prop.DatabaseColumn.DataType == typeof(DateTime))
                    sqLiteDatePatch = ".ToString(\"yyyy-MM-dd HH:mm:ss\")";

                sbDataAccessClassUpdateMethodParametersAssignationSqlIte.Append(_templateProvider.GetDataAccessClassGenericMethodParameterToDbCommand(propertiesCounter, "@" + prop.DatabaseColumn.ColumnName, prop.DatabaseColumn.DataType.Name, "OwnData." + prop.PropertyName + sqLiteDatePatch, "            "));

                sbDataAccessClassRemoveMethodsParametersAssignation.Append(_templateProvider.GetDataAccessClassGenericMethodParameterToDbCommand(primaryKeyColumnsCounter, "@" + prop.DatabaseColumn.ColumnName, prop.DatabaseColumn.DataType.Name, "OwnData." + prop.PropertyName, "                "));
                primaryKeyColumnsCounter++;
            }
            
            if (NewClass.Properties.Count > 0)//                                                                     + ","
            {
                sbDataAccessClassUpdateMethodColumns.Remove(sbDataAccessClassUpdateMethodColumns.ToString().LastIndexOf(","), 2);

                sbDataAccessClassColumnNames.Remove(sbDataAccessClassColumnNames.Length - 2, 2);
                sbDataAccessClassParametersNamesUpdateMethod.Remove(sbDataAccessClassParametersNamesUpdateMethod.Length - 2, 2);
                sbDataAccessClassParametersNamesInsertMethod.Remove(sbDataAccessClassParametersNamesInsertMethod.Length - 2, 2);
                
                sbDataAccessClassInsertMethodParametersAssignation.Remove(sbDataAccessClassInsertMethodParametersAssignation.Length - 2, 2);
                sbDataAccessClassUpdateMethodParametersAssignationSqlIte.Remove(sbDataAccessClassUpdateMethodParametersAssignationSqlIte.Length - 2, 2);
            }
            
            //CreateFromDataRowMethod
            sbDataAccessClassCreateFromDataRow = _templateProvider.GetDataAccessClassCreateFromDataRowMethodSourceCode(NewClass, _model);
            sbDataAccessClassCreateFromDataRow = sbDataAccessClassCreateFromDataRow.Replace("{PropertiesAssignation}", sbDataAccessClassCreateFromDataRowMethodColumns.ToString());
            
            //UpdateMethod
            switch (_databaseEngine)
            {
                case DatabaseEngines.SqlLite:
                    {
                        sbDataAccessClassUpdateMethod = _templateProvider.GetDataAccessClassUpdateMethodSourceCodeSQLite(NewClass.ClassName, _model, GetPrimaryKeyWhereCondition(NewClass.DatabaseTable), NewClass.DatabaseTable.TableName, sbDataAccessClassUpdateMethodParametersAssignationSqlIte.ToString());
                        break;
                    }
            case DatabaseEngines.SqlServer:
                    {
                        sbDataAccessClassUpdateMethod = _templateProvider.GetDataAccessClassUpdateMethodSourceCodeSqlServer(NewClass.ClassName, NewClass.DatabaseTable.TableName, GetDataAccessClassUpdateMethodParametersAssignationSqlServer(NewClass));
                        break;
                    }
        
                default:
                    throw new ArgumentOutOfRangeException();
            }
            
            sbDataAccessClassUpdateMethod = sbDataAccessClassUpdateMethod.Replace("{PropertiesAssignation}", sbDataAccessClassUpdateMethodColumns.ToString());
            sbDataAccessClassUpdateMethod = sbDataAccessClassUpdateMethod.Replace("{ParamsCount}", (NewClass.Properties.Count + NewClass.DatabaseTable.PrimaryKey.Count()).ToString());
            sbDataAccessClassUpdateMethod = sbDataAccessClassUpdateMethod.Replace("{TableName}", NewClass.DatabaseTable.TableName);

            //InsertMethod
            switch (_databaseEngine)
            {
                case DatabaseEngines.SqlLite:
                    {
                        sbDataAccessClassInsertMethod = _templateProvider.GetDataAccessClassInsertMethodSourceCodeSqlIte(NewClass.ClassName, _model, GetPrimaryKeyWhereCondition(NewClass.DatabaseTable), NewClass.DatabaseTable.TableName, sbDataAccessClassInsertMethodParametersAssignation.ToString(), sbDataAccessClassParametersNamesInsertMethod.ToString());
                        break;
                    }
                case DatabaseEngines.SqlServer:
                    {
                        sbDataAccessClassInsertMethod = _templateProvider.GetDataAccessClassInsertMethodSourceCodeSqlServer(NewClass.ClassName, NewClass.DatabaseTable.TableName, GetDataAccessClassInsertMethodParametersAssignationSqlServer(NewClass), GetDataAccessClassInsertMethodAutogeneratedIDAssignation(NewClass));
                        break;
                    }
                default:
                    throw new ArgumentOutOfRangeException();
            }
            
            //sbDataAccessClassInsertMethod = sbDataAccessClassInsertMethod.Replace("{ParametersValues}", sbDataAccessClassInsertMethodColumns.ToString());
            sbDataAccessClassInsertMethod = sbDataAccessClassInsertMethod.Replace("{CheckIfPrimaryKeyIsSupplied}", sbDataAccessClassCheckIfPrimaryKeyIsSupplied.ToString());
            sbDataAccessClassInsertMethod = sbDataAccessClassInsertMethod.Replace("{AutoGeneratedIdsToDataClassAssignation}",sbDataAccessClassAutoGeneratedIdsToDataClassAssignation.ToString());
            sbDataAccessClassInsertMethod = sbDataAccessClassInsertMethod.Replace("{ParamsCount}", NewClass.Properties.Count.ToString());
            sbDataAccessClassInsertMethod = sbDataAccessClassInsertMethod.Replace("{TableName}", NewClass.DatabaseTable.TableName);
            
            //GetAllMethod
            sbDataAccessClassGetAllMethod = _templateProvider.GetDataAccessClassGetAllMethod(NewClass.ClassName, NewClass.DatabaseTable.TableName);
            sbDataAccessClassRemoveMethods = _templateProvider.GetDataAccessClassRemoveMethod(NewClass.ClassName, _model, GetPrimaryKeyWhereCondition(NewClass.DatabaseTable), NewClass.DatabaseTable.TableName, sbDataAccessClassRemoveMethodsParametersAssignation.ToString(), sbDataAccessClassParametersNamesInsertMethod.ToString());
            sbDataAccessClassRemoveMethods = sbDataAccessClassRemoveMethods.Replace("{ParamsCount}", NewClass.DatabaseTable.PrimaryKey.Count.ToString());
            
            //Fill the virtual properties to reflect objects relations
            foreach (IForeignKey foreignKey in NewClass.DatabaseTable.ForeignKeys)
            {
                foreignKey.SetSourceCode(OutputType.Class, _templateProvider.GetForeignKeyPropertySourceCode(foreignKey, _model));
                sbForeignKeysProperties.Append(foreignKey.GetSourceCode(OutputType.Class));
                sbClassForeignKeysPrivateVariables.Append(_templateProvider.GetForeignKeyLocalVariableSourceCode(foreignKey, _model));
            }

            foreach (IConstructor constructor in NewClass.Constructors)
            {
                constructor.SetSourceCode(OutputType.Class, _templateProvider.GetClassConstructorSourceCode(constructor, _model));
                sbClassConstructors.Append(constructor.GetSourceCode(OutputType.Class));


                sbDataAccessClassDataLoaders.Append(_templateProvider.GetDataAccessClassLoaderSourceCode(constructor, _model,_databaseEngine));

            }
           
            sbDataAccessClassPrivateVariables = _templateProvider.GetDataAccessClassPrivateVariables();

            _classSourceCode = _templateProvider.GetClassSourceCode(NewClass.ClassName, _model, sbClassConstructors.ToString(),
                                                            sbClassPrivateVariables.ToString(), sbClassPublicProperties.ToString(),
                                                            sbForeignKeysProperties.ToString(),
                                                            sbClassForeignKeysPrivateVariables.ToString());

            _dataClassSourceCode = _templateProvider.GetDataClassSourceCode(NewClass.ClassName, _model, sbDataClassConstructors.ToString(),
                                                            sbDataClassPrivateVariables.ToString(), sbDataClassPublicProperties.ToString(),
                                                            sbForeignKeysProperties.ToString(),
                                                            sbClassForeignKeysPrivateVariables.ToString());

            _dataAccessClassSourceCode = _templateProvider.GetDataAccessClassSourceCode(NewClass.ClassName, _model, sbDataAccessClassConstructors.ToString(),
                                                            sbDataAccessClassPrivateVariables, sbDataClassPublicProperties.ToString(),
                                                            sbForeignKeysProperties.ToString(),
                                                            sbClassForeignKeysPrivateVariables.ToString(), sbDataAccessClassDataLoaders.ToString(),
                                                            sbDataAccessClassCreateFromDataRow, sbDataAccessClassUpdateMethod,
                                                            sbDataAccessClassColumnNames.ToString(), sbDataAccessClassInsertMethod, sbDataAccessClassGetAllMethod, sbDataAccessClassRemoveMethods, DataBaseSpecificUsings);

            MultipleTargetObject MyClass = (MultipleTargetObject)NewClass;
            MyClass.SetSourceCode(OutputType.Class, _classSourceCode);
            MyClass.SetSourceCode(OutputType.DataClass, _dataClassSourceCode);
            MyClass.SetSourceCode(OutputType.DataAccessClass, _dataAccessClassSourceCode);
        }

        private string GetDataAccessClassUpdateMethodParametersAssignationSqlServer(IGeneratorClass newClass)
        {
            string result = "";
            foreach (var prop in newClass.Properties)
            {
                result += "             SqlParameter param" + prop.PropertyName + " = new SqlParameter(\"@" + prop.DatabaseColumn.ColumnName + "\", SqlDbType." + GetDBType(prop.DatabaseColumn.DataType) + ");" + Environment.NewLine;
                result += "             param" + prop.PropertyName + ".Direction = ParameterDirection.Input;" + Environment.NewLine;
                result += "             param" + prop.PropertyName + ".Value = OwnData."+ prop.PropertyName + ";" + Environment.NewLine;
                result += "             cmd.Parameters.Add(param" + prop.PropertyName + ");" + Environment.NewLine  +Environment.NewLine;
            }
            return result;
        }

        private SqlDbType GetDBType(System.Type theType)
        {
            SqlParameter p1;
            System.ComponentModel.TypeConverter tc;
            p1 = new SqlParameter();
            tc = System.ComponentModel.TypeDescriptor.GetConverter(p1.DbType);
            if (tc.CanConvertFrom(theType))
            {
                p1.DbType = (DbType) tc.ConvertFrom(theType.Name);
            }
            else
            {
                //Try brute force
                try
                {
                    p1.DbType = (DbType) tc.ConvertFrom(theType.Name);
                }
                catch (Exception ex)
                {
                    //Do Nothing
                }
            }
            return p1.SqlDbType;
        }

        private string GetDataAccessClassInsertMethodAutogeneratedIDAssignation(IGeneratorClass newClass)
        {
            string result = "";
            foreach (var col in newClass.DatabaseTable.Columns.Where(r=> r.IsIdentity))
            {
                IProperty prop = (from n in newClass.Properties where n.DatabaseColumn.ColumnName == col.ColumnName select n).Single();
                result += "             OwnData." + prop.PropertyName + " = (" + prop.DatabaseColumn.DataType.Name + ")param" + prop.PropertyName + ".Value;" + Environment.NewLine;
            }
            return result;
        }

        private string GetDataAccessClassInsertMethodParametersAssignationSqlServer(IGeneratorClass newClass)
        {
            string result = "";
            foreach (var prop in newClass.Properties)
            {

                result += "             SqlParameter param" + prop.PropertyName + " = new SqlParameter(\"@" + prop.DatabaseColumn.ColumnName + "\", SqlDbType." + GetDBType(prop.DatabaseColumn.DataType) + ");" + Environment.NewLine;
                //result += "            param" + col.ColumnName + ".TypeName = SqlDbType." + GetDBType(col.DataType)+ ";" + Environment.NewLine;
                if (prop.DatabaseColumn.IsIdentity)
                {   
                    result += "             param" + prop.PropertyName + ".Direction = ParameterDirection.Output;" + Environment.NewLine;
                    result += "             cmd.Parameters.Add(param" + prop.PropertyName + ");" + Environment.NewLine;
                }
                else
                {
                    result += "             param" + prop.PropertyName + ".Direction = ParameterDirection.Input;" + Environment.NewLine;
                    result += "             param" + prop.PropertyName + ".Value = OwnData." + prop.PropertyName + ";" + Environment.NewLine;
                    result += "             cmd.Parameters.Add(param" + prop.PropertyName + "); " + Environment.NewLine;
                }
            }
            return result;
        }

        private string GetPropertyNewGeneratedAutoincrementalValue(Property Prop)
        {
            if (Prop.DatabaseColumn.DataType == typeof (Int16) ||
                Prop.DatabaseColumn.DataType == typeof (Int32) ||
                Prop.DatabaseColumn.DataType == typeof (Int64) ||
                Prop.DatabaseColumn.DataType == typeof (Byte) ||
                Prop.DatabaseColumn.DataType == typeof (Double) ||
                Prop.DatabaseColumn.DataType == typeof (Single) ||
                Prop.DatabaseColumn.DataType == typeof (Decimal)
                )
                return "            if(OwnData." + Prop.PropertyName + " == " + TemplateProvider.GetDefaultValue(Prop.DatabaseColumn.DataType) + ") OwnData." + Prop.PropertyName + " = (" + Prop.DatabaseColumn.DataType.Name + ")connector.GetTableMaxColumnValue(\"" + Prop.ContainerTable.TableName + "\", \"" + Prop.DatabaseColumn.ColumnName + "\");" + Environment.NewLine;
            return "";
        }

        private string GetPropertyDefaultValueChecker(Property Prop)
        {
          //  return "OwnData." + Prop.PropertyName + " == " + TemplateProvider.GetDefaultValue(Prop.DatabaseColumn.DatabaseType);

            if (Prop.DatabaseColumn.DataType == typeof(Int16) ||
              Prop.DatabaseColumn.DataType == typeof(Int32) ||
              Prop.DatabaseColumn.DataType == typeof(Int64) ||
              Prop.DatabaseColumn.DataType == typeof(Byte) ||
              Prop.DatabaseColumn.DataType == typeof(Double) ||
              Prop.DatabaseColumn.DataType == typeof(Single)
              )
            {
                return "";//"            if(OwnData." + Prop.PropertyName + " == " + TemplateProvider.GetDefaultValue(Prop.DatabaseColumn.DatabaseType) + ") OwnData." + Prop.PropertyName + " = (" + Prop.DatabaseColumn.DatabaseType.Name + ")connector.GetTableMaxColumnValue(\"" + Prop.ContainerTable.TableName + "\", \"" + Prop.ContainerTable.TableName + "\");";
            }
            if (Prop.DatabaseColumn.DataType == typeof(String) ||
                Prop.DatabaseColumn.DataType == typeof(Guid)||

                Prop.DatabaseColumn.DataType == typeof(DateTime))
            {
                return "            if(OwnData." + Prop.PropertyName + " == " + TemplateProvider.GetDefaultValue(Prop.DatabaseColumn.DataType) + ") throw new InvalidOperationException(\"On tables using non numeric columns as primary key, is not possible to execute the Insert statement until that colum values are different than his type default value. Affected Table: " + Prop.ContainerTable.TableName + ", Column: " + Prop.DatabaseColumn.ColumnName + " of type: " + Prop.DatabaseColumn.DataType.Name + "\");" + Environment.NewLine;
            }

            if (Prop.DatabaseColumn.DataType == typeof(Boolean) ||
                Prop.DatabaseColumn.DataType == typeof(Decimal))
            {
                return "/* '" + Prop.DatabaseColumn.DataType + "' column [" + Prop.DatabaseColumn.ColumnName + "] on Primary Key cannot be checked!*/" + Environment.NewLine; ;
                //return Prop.DatabaseColumn.ColumnName + " == " + TemplateProvider.GetDefaultValue(Prop.DatabaseColumn.DatabaseType);
            }

            //log something here
            throw new ApplicationException("No PropertyDefaultValueChecker value for property type:" + Prop.DatabaseColumn.DataType.Name);
        }

        private string GetDataAccessClassUpdateMethodVariablesSetter(IProperty Prop)
        {
            return "[" + Prop.DatabaseColumn.ColumnName + "] = @" + Prop.DatabaseColumn.ColumnName + ", ";
        }

        private string GetDataAccessClassInsertMethodVariablesSetter(IProperty Prop)
        {
            if (Prop.DatabaseColumn.DataType == typeof(Int16) ||
                Prop.DatabaseColumn.DataType == typeof(Int32) ||
                Prop.DatabaseColumn.DataType == typeof(Int64) ||
                Prop.DatabaseColumn.DataType == typeof(Byte) ||
                Prop.DatabaseColumn.DataType == typeof(Double) ||
                Prop.DatabaseColumn.DataType == typeof(Single) ||
                Prop.DatabaseColumn.DataType == typeof(Decimal)
                )
                return Prop.DatabaseColumn.ColumnName + " = \" + OwnData." + Prop.PropertyName + " AND ";
                //return "         if (dt[\"" + Prop.DatabaseColumn.ColumnName + "\"] != null) result." + Prop.PropertyName + " = " + Prop.DatabaseColumn.DatabaseType.Name + ".Parse(dt[\"" + Prop.DatabaseColumn.ColumnName + "\"].ToString());" + Environment.NewLine;

            if (Prop.DatabaseColumn.DataType == typeof(String) ||
                Prop.DatabaseColumn.DataType == typeof(Guid))
            //Prop.DatabaseColumn.DatabaseType == typeof(Binary))
            {
                return Prop.DatabaseColumn.ColumnName + " = '\" + OwnData." + Prop.PropertyName + "' AND ";
            }

            if (Prop.DatabaseColumn.DataType == typeof(Boolean))
            {
                return Prop.DatabaseColumn.ColumnName + " = \" + OwnData." + Prop.PropertyName + " AND ";
            }

            if (Prop.DatabaseColumn.DataType == typeof(DateTime))
            {
                return Prop.DatabaseColumn.ColumnName + " = \" + OwnData." + Prop.PropertyName + " AND ";
            }
            //log something here
            return "";
        }

        private string GetDataAccessClassCreateFromDataRowVariablesInitialization(IProperty Prop)
        {
            if (Prop.DatabaseColumn.DataType == typeof(Int16) || 
                Prop.DatabaseColumn.DataType == typeof (Int32) || 
                Prop.DatabaseColumn.DataType == typeof (Int64) ||
                Prop.DatabaseColumn.DataType == typeof (Byte)  ||
                Prop.DatabaseColumn.DataType == typeof (Double) ||
                Prop.DatabaseColumn.DataType == typeof (Single) ||
                Prop.DatabaseColumn.DataType == typeof(Decimal) || Prop.DatabaseColumn.DataType == typeof(Boolean)
                )
                return "            if (dt[\"" + Prop.DatabaseColumn.ColumnName + "\"] != null) result." + Prop.PropertyName + " = " + Prop.DatabaseColumn.DataType.Name + ".Parse(dt[\"" + Prop.DatabaseColumn.ColumnName + "\"].ToString());" + Environment.NewLine;
            
            if (Prop.DatabaseColumn.DataType == typeof (String) ||
                Prop.DatabaseColumn.DataType == typeof (Guid))
                //Prop.DatabaseColumn.DatabaseType == typeof(Binary))
            {
                return "            if (!String.IsNullOrEmpty(dt[\"" + Prop.DatabaseColumn.ColumnName + "\"].ToString())) result." + Prop.PropertyName + " = dt[\"" + Prop.DatabaseColumn.ColumnName + "\"].ToString();" + Environment.NewLine;
            }

            //if (Prop.DatabaseColumn.DataType == typeof (Boolean))
            //{
            //    return "            if (dt[\"" + Prop.DatabaseColumn.ColumnName + "\"] != null) result." + Prop.PropertyName + " = (bool)" + Prop.DatabaseColumn.DataType.Name + ";" + Environment.NewLine;
            //}

            if (Prop.DatabaseColumn.DataType == typeof (DateTime))
            {       //!dt.IsNull("LastLogin"))
                return "            if (!dt.IsNull(\"" + Prop.DatabaseColumn.ColumnName + "\")) result." + Prop.PropertyName + " = (DateTime)dt[\"" + Prop.DatabaseColumn.ColumnName + "\"];" + Environment.NewLine;
            }
            //log something here
            return "";
        
        }

        private void SaveClassSourceCode(IGeneratorClass NewClass)
        {
            var multipleTargetObject = ((MultipleTargetObject)NewClass);
            Common.IO.WriteTextFile(_OutputFolderProject + NewClass.ClassName + ".cs", multipleTargetObject.GetSourceCode(OutputType.Class));
            Common.IO.WriteTextFile(_OutputFolderProject + NewClass.ClassName + "Data.cs", multipleTargetObject.GetSourceCode(OutputType.DataClass));
            Common.IO.WriteTextFile(_OutputFolderProject + NewClass.ClassName + "DAL.cs", multipleTargetObject.GetSourceCode(OutputType.DataAccessClass));
        }

        public void SaveClassesSourceCode()
        {
            foreach (IGeneratorClass newClass in _model.Classes)
            {
                SaveClassSourceCode(newClass);
            }
        }

        public void SaveDataConnector()
        {
            string strSqlConnectorSourceCode = "";
            strSqlConnectorSourceCode = _templateProvider.GetSQLConnectorSourceCode(_model, _databaseEngine);
            Common.IO.WriteTextFile(_OutputFolderProject + "SqlConnector.cs", strSqlConnectorSourceCode);
        }

        public void SaveProjectSourceCode()
        {
            //Project File
            String projectSource = GenerateProjectSourceCode();

            //ExternalReferences
            if (!Directory.Exists(_OutputFolderProject + "External"))
                Directory.CreateDirectory(_OutputFolderProject + "External");

            projectSource = projectSource.Replace("{References}", GetReferencesSourceCode());

            Common.IO.WriteTextFile(_OutputFolderProject + _model.NameSpace + ".csproj", projectSource);

            //Assembly Info
            if (!Directory.Exists(_OutputFolderProject + "Properties"))
                Directory.CreateDirectory(_OutputFolderProject + "Properties");
            Common.IO.WriteTextFile(_OutputFolderProject + "Properties\\AssemblyInfo.cs", GenerateAssemblyInfo());



            foreach (string file in Directory.GetFiles(Common.Templating.GetExternalReferencesPath()))
            {
                FileInfo fn = new FileInfo(file);
                File.Copy(fn.FullName, _OutputFolderProject + "External\\" + fn.Name, true);
            }

        }

        public void SaveProjectSampleSourceCode()
        {
            //Project File
            String projectSampleSource = GenerateProjectSampleSourceCode();
            String projectSample_ProgramCS = GenerateProjectSample_ProgramCS_SourceCode(); //
            String projectSample_MainFormCS = GetSampleProject_MainFormCS_SourceCode(); //
            String projectSample_MainFormRESX = GetSampleProject_MainFormRESX_SourceCode(); //
            String projectSample_Solution = GetSampleProject_Solution_SourceCode(); //
            
            //ExternalReferences
            if (!Directory.Exists(_OutputFolderSampleProject + "External"))
                Directory.CreateDirectory(_OutputFolderSampleProject + "External");

            //Automatically add all assemblies found on external directory, initially I let is as it is on the main project
            projectSampleSource = projectSampleSource.Replace("{References}", GetReferencesSourceCode()); 

            Common.IO.WriteTextFile(_OutputFolderSampleProject + _model.NameSpace + ".SampleProject.csproj", projectSampleSource);
            
            //Assembly Info
            if (!Directory.Exists(_OutputFolderSampleProject + "Properties"))
                Directory.CreateDirectory(_OutputFolderSampleProject + "Properties");
            Common.IO.WriteTextFile(_OutputFolderSampleProject + "Properties\\AssemblyInfo.cs", GenerateAssemblyInfo());

            Common.IO.WriteTextFile(_OutputFolderSampleProject + "Program.cs",projectSample_ProgramCS);
            Common.IO.WriteTextFile(_OutputFolderSampleProject + "MainForm.cs", projectSample_MainFormCS);
            Common.IO.WriteTextFile(_OutputFolderSampleProject + "MainForm.resx", projectSample_MainFormRESX);
            Common.IO.WriteTextFile(_OutputFolder + _model.NameSpace + ".sln", projectSample_Solution);
            
            

            foreach (string file in Directory.GetFiles(Common.Templating.GetExternalReferencesPath()))
            {
                FileInfo fn = new FileInfo(file);
                File.Copy(fn.FullName, _OutputFolderSampleProject + "External\\" + fn.Name, true);
            }


            //Program.cs:
            //ProjectSample_Program.cs.tpl


            
        }

        private string GetReferencesSourceCode()
        {
            StringBuilder sb = new StringBuilder();
            
            foreach (string file in Directory.GetFiles(Common.Templating.GetExternalReferencesPath()))
            {
                FileInfo fn = new FileInfo(file);
                File.Copy(fn.FullName, _OutputFolderProject + "External\\" + fn.Name, true);

                try
                {
                    /*
                    "<Reference Include=\"log4net, Version=1.2.11.0, Culture=neutral, PublicKeyToken=669e0ddf0bb1aa2a, processorArchitecture=MSIL\">"
                      <SpecificVersion>False</SpecificVersion>
                      <HintPath>External\log4net.dll</HintPath>
                    </Reference>
                     
                     */
                    Assembly a = System.Reflection.Assembly.ReflectionOnlyLoadFrom(file);
                    sb.AppendLine("<Reference Include=\""+ a.ToString() + ", processorArchitecture=MSIL\">");
                    sb.AppendLine("     <SpecificVersion>False</SpecificVersion>");
                    sb.AppendLine("     <HintPath>External\\" + fn.Name + "</HintPath>");
                    sb.AppendLine("</Reference>");
                    
            
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                    //File was probably not an assembly, nothing to do.
                    return "";
                }

            }
            return sb.ToString();
        }
        public enum StoredProcedureType
        {
            Insert = 0,
            Update = 1,
            Delete
        }
        public enum StoredProcedureAction
        {
            Drop = 0,
            Create = 1
        }
        public void SaveStoredProcedures()
        {
            String sps = GenerateStoredProcedures();
            Common.IO.WriteTextFile(_OutputFolderProject + "StoredProcedures.sql",sps);
        }

        private string GenerateStoredProcedures()
        {
            StringBuilder sb = new StringBuilder();
            string separator = Environment.NewLine + "GO" + Environment.NewLine;
            foreach (IGeneratorClass generatorClass in _model.Classes)
            {
                sb.Append(GenerateStoredProceduresSourceCode(generatorClass, StoredProcedureType.Insert, StoredProcedureAction.Drop));
                sb.Append(separator);
                sb.Append(GenerateStoredProceduresSourceCode(generatorClass, StoredProcedureType.Insert, StoredProcedureAction.Create));
                sb.Append(separator);
                sb.Append(GenerateStoredProceduresSourceCode(generatorClass, StoredProcedureType.Update, StoredProcedureAction.Drop));
                sb.Append(separator);
                sb.Append(GenerateStoredProceduresSourceCode(generatorClass, StoredProcedureType.Update, StoredProcedureAction.Create));
                sb.Append(separator);
                sb.Append(GenerateStoredProceduresSourceCode(generatorClass, StoredProcedureType.Delete, StoredProcedureAction.Drop));
                sb.Append(separator);
                sb.Append(GenerateStoredProceduresSourceCode(generatorClass, StoredProcedureType.Delete, StoredProcedureAction.Create));
                sb.Append(separator);
            }
            return sb.ToString();
        }

        private string GenerateStoredProceduresSourceCode(IGeneratorClass generatorClass, StoredProcedureType InsertOrUpdate, StoredProcedureAction Action)
        {
            StringBuilder result = new StringBuilder();
            switch (InsertOrUpdate)
            {
                case StoredProcedureType.Insert:
                    {
                        if (Action== StoredProcedureAction.Drop)
                            return _templateProvider.GetDataBasesDropStoredProcedure(generatorClass, InsertOrUpdate);
                        if (Action == StoredProcedureAction.Create)
                            return _templateProvider.GetDataBasesInsertStoredProcedure(generatorClass, _model);
                        break;
                    }
                case StoredProcedureType.Update:
                    {
                        if (Action == StoredProcedureAction.Drop)
                            return _templateProvider.GetDataBasesDropStoredProcedure(generatorClass, InsertOrUpdate);
                        if (Action == StoredProcedureAction.Create)
                            return _templateProvider.GettDataBasesUpdateStoredProcedure(generatorClass, _model);
                        break;
                    }
                case StoredProcedureType.Delete:
                    {
                        if (Action == StoredProcedureAction.Drop)
                            return _templateProvider.GetDataBasesDropStoredProcedure(generatorClass, InsertOrUpdate);
                        if (Action == StoredProcedureAction.Create)
                            return _templateProvider.GettDataBasesDeleteStoredProcedure(generatorClass, _model);
                        break;
                    }
                default:
                    throw new ArgumentOutOfRangeException("InsertOrUpdate");
            }
            return result.ToString();
        }
    }

    
}